﻿using Microsoft.Data.Sqlite;
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Reflection;
using System.Data;

namespace Common_ClassLibrary
{
    public class Sqlite64Helper : IDisposable
    {
        /// <summary>
        /// 连接字符串
        /// </summary>
        public string ConStr { get; private set; }

        /// <summary>
        /// 连接数据库对象池
        /// </summary>
        private Queue<SqliteConnection> ConQueue;

        /// <summary>
        /// 执行对象池
        /// </summary>
        private Queue<SqliteCommand> ComQueue;

        /// <summary>
        /// 线程互斥对象
        /// </summary>
        public Mutex CurMutex { get; set; }

        /// <summary>
        /// 实体类中属性的字典
        /// </summary>
        private ConcurrentDictionary<Type, List<PropertyInfo>> EntityPropDic;

        public Sqlite64Helper(string dataBaseLinkStr)
        {
            try
            {
                ConStr = dataBaseLinkStr;
                ConQueue = new Queue<SqliteConnection>();
                ComQueue = new Queue<SqliteCommand>();
                EntityPropDic = new ConcurrentDictionary<Type, List<PropertyInfo>>();
                CurMutex = new Mutex();
                EnConnectionQueue(CreateCon());
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private SqliteConnection CreateCon()
        {
            SqliteConnection con = new SqliteConnection(ConStr);
            return con;
        }

        private void EnConnectionQueue(SqliteConnection con)
        {
            if (con == null)
            {
                return;
            }
            CurMutex.WaitOne();
            ConQueue.Enqueue(con);
            CurMutex.ReleaseMutex();
        }

        private SqliteConnection DeConnectionQueue()
        {
            SqliteConnection con = null;
            CurMutex.WaitOne();
            if (ConQueue.Count > 0)
            {
                con = ConQueue.Dequeue();
            }
            CurMutex.ReleaseMutex();
            if (con == null)
            {
                con = CreateCon();
            }
            if (con.State != System.Data.ConnectionState.Open)
            {
                con.Open();
            }
            return con;
        }

        private void EnCommondQueue(SqliteCommand com)
        {
            CurMutex.WaitOne();
            com.Connection = null;
            com.CommandText = null;
            com.Parameters.Clear();
            ComQueue.Enqueue(com);
            CurMutex.ReleaseMutex();
        }

        private SqliteCommand DeCommondQueue()
        {
            SqliteCommand com = null;
            CurMutex.WaitOne();
            if (ComQueue.Count > 0)
            {
                com = ComQueue.Dequeue();
            }
            CurMutex.ReleaseMutex();
            if (com == null)
            {
                com = new SqliteCommand();
            }
            return com;
        }

        private object ExecuteMethodByType(ExecuteType type, string sqlStr, Dictionary<string, object> paramDic)
        {
            object objResult = null;

            //获取连接对象
            var con = DeConnectionQueue();
            var com = DeCommondQueue();

            com.CommandText = sqlStr;
            com.Parameters.AddRange(FormatParameterByDic(paramDic));
            com.Connection = con;
            switch (type)
            {
                case ExecuteType.Null:
                    break;
                case ExecuteType.ExecuteNonQuery:
                    objResult = com.ExecuteNonQuery();
                    break;
                case ExecuteType.ExecuteScalar:
                    objResult = com.ExecuteScalar();
                    break;
                //因为reader要独占一个连接应该在 数据处理完成 后方法内部自己去 回收
                case ExecuteType.ExecuteReader:
                    //用完这个对象要释放 
                    // objResult = com.ExecuteReader();
                    break;
                default:
                    break;
            }

            EnCommondQueue(com);
            //用完以后要回收连接对象
            EnConnectionQueue(con);


            return objResult;
        }

        public int ExecuteNoQueue(string sqlStr, Dictionary<string, object> paramDic)
        {
            return (int)ExecuteMethodByType(ExecuteType.ExecuteNonQuery, sqlStr, paramDic);
        }

        public object ExecuteScalar(string sqlStr, Dictionary<string, object> paramDic)
        {
            return ExecuteMethodByType(ExecuteType.ExecuteScalar, sqlStr, paramDic);
        }

        public List<T> ExecuteReader<T>(string sqlStr, Dictionary<string, object> paramDic)
        {
            List<T> resultLs = new List<T>();
            var con = DeConnectionQueue();
            var com = DeCommondQueue();

            com.CommandText = sqlStr;
            com.Parameters.AddRange(FormatParameterByDic(paramDic));
            com.Connection = con;

            SqliteDataReader dataReader = com.ExecuteReader();

            if (dataReader == null || (!dataReader.HasRows))
            {
                return resultLs;
            }
            Type curType = typeof(T);
            List<PropertyInfo> propLs = new List<PropertyInfo>();
            if (EntityPropDic.ContainsKey(curType))
            {
                propLs = EntityPropDic[curType];
            }
            else
            {
                propLs.AddRange(curType.GetProperties());
                EntityPropDic[curType] = propLs;
            }

            while (dataReader.Read())
            {
                var entity = Activator.CreateInstance(curType);
                propLs.ForEach(ret =>
                {
                    if (typeof(Int64) == ret.PropertyType)
                    {
                        ret.SetValue(entity, Convert.ToInt64(dataReader[ret.Name.ToLower()]));
                    }
                    else if (typeof(string) == ret.PropertyType)
                    {
                        ret.SetValue(entity, dataReader[ret.Name.ToLower()].ToString());
                    }
                    else if (typeof(int) == ret.PropertyType)
                    {
                        ret.SetValue(entity, Convert.ToInt32(dataReader[ret.Name.ToLower()]));
                    }
                    else if (typeof(float) == ret.PropertyType)
                    {
                        ret.SetValue(entity, Convert.ToSingle(dataReader[ret.Name.ToLower()]));
                    }
                    else
                    {
                        ret.SetValue(entity, dataReader[ret.Name.ToLower()]);
                    }
                });
                resultLs.Add((T)entity);
            }
            //不要忘记释放reader和回收con 和com
            dataReader.Close();
            EnCommondQueue(com);
            EnConnectionQueue(con);
            return resultLs;
        }

        /// <summary>
        /// 用完请自行释放datareader
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <param name="paramDic"></param>
        /// <returns></returns>
        public SqliteDataReader ExecuteReader(string sqlStr, Dictionary<string, object> paramDic)
        {
            var con = DeConnectionQueue();
            var com = DeCommondQueue();

            com.CommandText = sqlStr;
            com.Parameters.AddRange(FormatParameterByDic(paramDic));
            com.Connection = con;

            SqliteDataReader dataReader = com.ExecuteReader(CommandBehavior.CloseConnection);
            return dataReader;
        }

        /*
         若果想实现，根据实体类型实现增删改，那就需要表和实体绑定，属性名称和表名称对应。 表和数据模型对应。 那就是ORM思想了
         */

        private List<SqliteParameter> FormatParameterByDic(Dictionary<string, object> paramDic)
        {
            List<SqliteParameter> ls = new List<SqliteParameter>();
            if (paramDic != null && paramDic.Count > 0)
            {
                foreach (var kvp in paramDic)
                {
                    ls.Add(new SqliteParameter(kvp.Key, kvp.Value));
                }
            }
            return ls;
        }

        public void Dispose()
        {
            while (true)
            {

                var con = DeConnectionQueue();
                var com = DeCommondQueue();
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
                if (com != null)
                {
                    com.Dispose();
                }
                if (con == null && com == null)
                {
                    break;
                }
            }
        }

        ~Sqlite64Helper()
        {
            Dispose();
        }
    }

    enum ExecuteType
    {
        Null,
        ExecuteNonQuery,
        ExecuteScalar,
        ExecuteReader,
    }
}
